package com.gisuni.sddc.dao;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.json.JSONArray;
import org.nutz.dao.Dao;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;

import com.gisuni.sddc.platformManager.dao.LayerTableNameDao;
import com.gisuni.sddc.util.UtilMethod;
@IocBean
public class RegionStaticDao{
	@Inject
	private Dao dao;
	
	@Inject
    private LayerTableNameDao layerTableNameDao;

	public Map<String, Object> staticLandNumAndArea(String tableName,String queryFild, String city, String county,String town){
		String sql="select count(*) num, sum("+queryFild+") area from "+tableName+" where "+queryFild+"<>0 AND CSMC='"+city+"'";
		if(!"".equals(county)){
			sql+=" AND FQMC='"+county+"'";
			if(!"".equals(town)){
				sql+=" AND ZJMC='"+town+"'";
			}
		}
		return getStaticResult(sql);
	}
	public Map<String, Object> staticExtendBlockNumAndArea(String tableName,String queryFild, String city, String county, String town){
		String sql="select count(*) num, sum(ZYDMJ) area from "+tableName+" where TZFS='"+queryFild+"' AND CS='"+city+"'";
		if(!"".equals(county)){
			sql+=" AND QJ='"+county+"'";
			if(!"".equals(town)){
				sql+=" AND ZJ='"+town+"'";
			}
		}
		return getStaticResult(sql);
	}
	public Map<String, Object> staticExtendBlockNumAndArea(String tableName,String queryFild, JSONArray managerUsers){
		/*String sql="select count(*) num, sum(ZYDMJ) area from "+tableName+" where TZFS='"+queryFild+"' AND CS='"+city+"'";
		if(!"".equals(county)){
			sql+=" AND QJ='"+county+"'";
		}*/
		String sql="select count(*) num, sum(ZYDMJ) area from "+tableName+" where TZFS='"+queryFild+"'";
		String CollectionPop="USERNAME IN (";
		
		for(int i=0;i<managerUsers.length();i++){
			if(i == managerUsers.length()-1){
				CollectionPop+="'"+managerUsers.getString(i)+"')";
			}else{
				CollectionPop+="'"+managerUsers.getString(i)+"',";
			}
		}
		sql +=" AND "+CollectionPop;
		System.out.println(sql);
		return getStaticResult(sql);
	}
	private Map<String, Object> getStaticResult(String sql){
		List<Map<String, Object>> lMap=UtilMethod.getFromSql(dao, sql);
		String num=(String)lMap.get(0).get("NUM");
		String area=(String)lMap.get(0).get("AREA");
		if(num == null || num.equals("")){
			lMap.get(0).put("NUM",0L);
		}else{
			lMap.get(0).put("NUM",Long.parseLong(num.trim()));
		}
		if(area == null || area.equals("")){
			lMap.get(0).put("AREA",0F);
		}else{
			lMap.get(0).put("AREA",Float.parseFloat(area.trim()));
		}
		return lMap.get(0);
	}
	/**
	 * 人员完成度的统计
	 * @param companyNameList
	 * @return
	 */
	public Map<String, Object> personCompletePercentStatic(Map<String,Object> map, String startDate, String endDate){
		//为人员数赋值
		//map=getPersonStaticNums(map);
		//为项目类型数赋值
		map=getProgramStaticNums(map,startDate,endDate);
		return map;
	}
	/**
	 * 获取统计公司下的人员数
	 * @param map
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getPersonStaticNums(Map<String,Object> map){
		String companyPersonNumTotalSQL="";
		companyPersonNumTotalSQL = "select orgTable.Organization_Name,userStaticTable.useNum from";
		companyPersonNumTotalSQL += " (select organization_company_id companyId,count(organization_company_id) useNum from sddcgisplatform.user_organization where organization_develop_id is not null and organization_company_id in (";
		companyPersonNumTotalSQL +=" select id from sddcgisplatform.organization where organization_type=1 and";
		String companyName="";
		Iterator<String> keys = map.keySet().iterator();
		while(keys.hasNext()){
			companyName=(String)keys.next();
			companyPersonNumTotalSQL+=" organization_name like '"+companyName+"%' or";
		}
		companyPersonNumTotalSQL=companyPersonNumTotalSQL.substring(0, companyPersonNumTotalSQL.length()-2);
		companyPersonNumTotalSQL+=") group by organization_company_id) userStaticTable";
		companyPersonNumTotalSQL+=" left join sddcgisplatform.organization  orgTable";
		companyPersonNumTotalSQL+=" on userStaticTable.companyId = orgTable.Id";
		List<Map<String, Object>> lMap=UtilMethod.getFromSql(dao, companyPersonNumTotalSQL);
		Iterator<String> keys1 = map.keySet().iterator();
		while(keys1.hasNext()){
			companyName=(String)keys1.next();
			boolean isExist=false;
			for(Map<String,Object> mapObject : lMap){
				if(mapObject.get("ORGANIZATION_NAME").toString().indexOf(companyName)>-1){
					isExist=true;
					((Map<String,Object>)map.get(companyName)).put("人员个数", mapObject.get("USENUM"));
					break;
				}
			}
			if(!isExist){
				((Map<String,Object>)map.get(companyName)).put("人员个数", 0);
			}
		}
		return map;
	}
	/**
	 * 分别为3种项目类型赋值
	 * @param map
	 * @return
	 */
	public Map<String, Object> getProgramStaticNums(Map<String,Object> map,String startDate, String endDate){
		Iterator<String> keys = map.keySet().iterator();
		Map<String, String> tableNamemap  = layerTableNameDao.getTableName();
		while(keys.hasNext()){
			String sql="";
			String companyName=(String)keys.next();
 
			sql+="select xmjd,count(xmjd) paramNums from "+tableNamemap.get("WYTZ")+" where username in (";
 
			sql+=" select user_name from  sddcgisplatform.user_info where user_id in(";
			sql+=" select user_id from sddcgisplatform.user_organization where organization_company_id=(";
			sql+=" select ID from sddcgisplatform.organization where organization_type=1 and organization_name like '"+companyName+"%'";
			sql+=" ) and organization_develop_id is not null) and user_type=1";
			sql+=" ) and DJRQ >= to_date('"+startDate+"','yyyy-mm-dd') and DJRQ <= to_date('"+endDate+"','yyyy-mm-dd') group by xmjd";
//			System.out.println(sql);
			map=getProgramStaticInfo(sql,map,companyName);
		}
		return map;
	}
	@SuppressWarnings("unchecked")
	public Map<String, Object> getProgramStaticInfo(String sql,Map<String,Object> map,String companyName){
		List<Map<String, Object>> lMap=UtilMethod.getFromSql(dao, sql);
		((Map<String,Object>)map.get(companyName)).put("核查项目", 0);
		((Map<String,Object>)map.get(companyName)).put("洽谈项目", 0);
		((Map<String,Object>)map.get(companyName)).put("获取项目", 0);
		((Map<String,Object>)map.get(companyName)).put("核查项目指标值", getPragramIndex(1,map,companyName));
		((Map<String,Object>)map.get(companyName)).put("洽谈项目指标值", getPragramIndex(2,map,companyName));
		
		for(Map<String,Object> mapObject : lMap){
			switch((String)mapObject.get("XMJD")){
				case "核查项目":
					((Map<String,Object>)map.get(companyName)).put("核查项目", mapObject.get("PARAMNUMS"));
					//((Map<String,Object>)map.get(companyName)).put("核查项目指标值", getPragramIndex(1,map,companyName));
					break;
				case "洽谈项目":
					((Map<String,Object>)map.get(companyName)).put("洽谈项目", mapObject.get("PARAMNUMS"));
					//((Map<String,Object>)map.get(companyName)).put("洽谈项目指标值", getPragramIndex(2,map,companyName));
					break;
				case "获取项目":
					((Map<String,Object>)map.get(companyName)).put("获取项目", mapObject.get("PARAMNUMS"));
					break;
			}
		}
		return map;
	}
	public Object getPragramIndex(int WYTZ_TYPE,Map<String,Object> map,String companyName){
		String sql="";
		sql+="select target from sddcgisplatform.v_org_company_target where WYTZ_TYPE = "+WYTZ_TYPE+" AND ORG_ID=(";
		sql+="select ID from sddcgisplatform.ORGANIZATION WHERE  organization_type=1 and organization_name  like '"+companyName+"%')";
		System.out.println(sql);
		List<Map<String, Object>> lMap=UtilMethod.getFromSql(dao, sql);
		if(lMap!=null && lMap.size()>0){
			return lMap.get(0).get("TARGET");
		}else{
			return 0;
		}
		
	}
}
